#include "dbsapmanager.h"
#include <QDebug>
#include <QVariant>
#include <QSqlQuery>
#include <QSqlDatabase>

DbSapManager::DbSapManager()
{
	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setHostName("localhost");
    db.setDatabaseName("D:/software/qt/exagon/earthquake/debug/structural.db3");
	db.setUserName("");
	db.setPassword("");
	bool ok = db.open();
    if (ok)
    {
		qDebug("Database aperto con successo !!!");
	}
    else if (!ok)
    {
		 qDebug("Database non aperto !!!");
	}
}

DbSapManager::~DbSapManager()
{
	qDebug("Database chiuso.");
}

int DbSapManager::SpecParamSearchID(SeismicLandmark& p, const unsigned short& id)
{
	QString sql, str;
	QSqlQuery query;
	sql.append("SELECT ");
	sql.append("ID, LON_WGS84, LAT_WGS84, ");
	sql.append("AG30, FO30, TO30, ");
	sql.append("AG50, FO50, TO50, ");
	sql.append("AG72, FO72, TO72, ");
	sql.append("AG101, FO101, TO101, ");
	sql.append("AG140, FO140, TO140, ");
	sql.append("AG201, FO201, TO201, ");
	sql.append("AG475, FO475, TO475, ");
	sql.append("AG975, FO975, TO975, ");
	sql.append("AG2475, FO2475, TO2475");
	sql.append(" FROM  sismici WHERE ID = ");
	sql.append(str.setNum(id));
	if (query.exec(sql))
	{
                if (!query.isActive()) { return ERR_QUERY_INACTIVE;	}
                query.first();
                if (!query.isValid()) { return ERR_QUERY_NOT_VALID; }
		p.setId(query.value(0).toDouble());
		p.set_lon(query.value(1).toDouble());
		p.set_lat(query.value(2).toDouble());
		p.set_SpecParam(
		SpecParam(query.value(3).toDouble(),query.value(4).toDouble(),query.value(5).toDouble()),
		SpecParam(query.value(6).toDouble(),query.value(7).toDouble(),query.value(8).toDouble()),
		SpecParam(query.value(9).toDouble(),query.value(10).toDouble(),query.value(11).toDouble()),
		SpecParam(query.value(12).toDouble(),query.value(13).toDouble(),query.value(14).toDouble()),
		SpecParam(query.value(15).toDouble(),query.value(16).toDouble(),query.value(17).toDouble()),
		SpecParam(query.value(18).toDouble(),query.value(19).toDouble(),query.value(20).toDouble()),
		SpecParam(query.value(21).toDouble(),query.value(22).toDouble(),query.value(23).toDouble()),
		SpecParam(query.value(24).toDouble(),query.value(25).toDouble(),query.value(26).toDouble()),
		SpecParam(query.value(27).toDouble(),query.value(28).toDouble(),query.value(29).toDouble())
								  );
		return ERR_OK;
	} else
	{
		 return ERR_QUERY_GENERIC;
	}
}

int DbSapManager::SpecParamOnGrid(SeismicGrid* grid){
	if (grid->site.isNull()) return ERR_SITE_NULL; // se sono settate le coordinate
	QString sql, str;
	QSqlQuery query;
	sql.append("SELECT ");
	sql.append("ID, LON_WGS84, LAT_WGS84, ");
	sql.append("AG30, FO30, TO30, ");
	sql.append("AG50, FO50, TO50, ");
	sql.append("AG72, FO72, TO72, ");
	sql.append("AG101, FO101, TO101, ");
	sql.append("AG140, FO140, TO140, ");
	sql.append("AG201, FO201, TO201, ");
	sql.append("AG475, FO475, TO475, ");
	sql.append("AG975, FO975, TO975, ");
	sql.append("AG2475, FO2475, TO2475");
    sql.append(" FROM  sismici"); // qDebug() << sql;
	query.exec(sql); if (!query.isActive()) { return ERR_QUERY_INACTIVE; }
	int nb_records = 0;
	query.first(); nb_records++; if (!query.isValid()) { return ERR_QUERY_NOT_VALID; }

	/************************************************/
	/*  CERCO I PRIMI DUE PUNTI PIU' VICINI AL SITO */
	/************************************************/
	SeismicLandmark P, P1, P2, P3, P4;
    double d, d1, d2; int err, err_db;

	P1.setId(query.value(0).toUInt());
	P1.set_lon(query.value(1).toDouble());
	P1.set_lat(query.value(2).toDouble());
	d1=grid->site.dist(P1,err);

	query.next(); nb_records++;
	P2.setId(query.value(0).toUInt());
	P2.set_lon(query.value(1).toDouble());
	P2.set_lat(query.value(2).toDouble());
	d2=grid->site.dist(P2,err);

	if (d1 > d2)
	{
		P = P1; P1 = P2; P2 = P;
		d = d1; d1 = d2; d2 = d;
	}

	while (query.next())
	{
		nb_records++;
		P.setId(query.value(0).toUInt());
		P.set_lon(query.value(1).toDouble());
		P.set_lat(query.value(2).toDouble());
		d=grid->site.dist(P,err);
		if (d <= d1)
		{
			P2 = P1; P1 = P;
			d2 = d1; d1 = d;
		}
		else if (d <= d2)
		{
			P2 = P;
			d2 = d;
		}
	}

	if ( nb_records != 10751 ) return ERR_RECORDS_NUMBERS;

	/************************************************/
	/*    ORDINO SULLA BASE DELL' ID DEL PUNTO      */
	/************************************************/

    if (P1.getId() > P2.getId())
	{
		P = P1; P1 = P2; P2 = P;
		d = d1; d1 = d2; d2 = d;
	}

	/************************************************/
	/*    VEDO LA POSIZIONE DEI PUNTI TRA LORO      */
	/************************************************/
	/*

			 P1 = P_TL ------------ P_TR = P2
				   |       (1)       |
				   |(222)       (222)|
				   |                 |
				   |       (1)       |
			 P3 = P_BL ------------ P_BR = P4

	 */

	// Riempio i valori dei punti trovati

	SpecParamSearchID(P1,P1.getId());
	SpecParamSearchID(P2,P2.getId());

	if ((P2.getId() - P1.getId()) == 1) // punti orizzontali
	{
		err_db = SpecParamSearchID(P,P1.getId()-222);
		if (err_db == ERR_OK)
		{
			d1=grid->site.dist(P,err);
		}
		else return err_db;

		err_db = SpecParamSearchID(P,P1.getId()+222);
		if (err_db == ERR_OK)
		{
			d2=grid->site.dist(P,err);
		}
		else return err_db;
		if (d1 < d2)
		{
            // Orizzontale sopra
			grid->P3 = P1;  grid->P4 = P2;

			err_db = SpecParamSearchID(P,P1.getId()-222);
			if (err_db != ERR_OK) return (err_db);
			grid->P1 = P;

			err_db = SpecParamSearchID(P,P2.getId()-222);
			if (err_db != ERR_OK) return (err_db);
			grid->P2 = P;

			return (ERR_OK);
		}
		else
		{
            // Orizzontale sotto
			grid->P1 = P1;  grid->P2 = P2;

			err_db = SpecParamSearchID(P,P1.getId()+222);
			if (err_db != ERR_OK) return (err_db);
			grid->P3 = P;

			err_db = SpecParamSearchID(P,P2.getId()+222);
			if (err_db != ERR_OK) return (err_db);
			grid->P4 = P;

			return (ERR_OK);
		}
	}
	else if ((P2.getId() - P1.getId()) == 222)  // punti verticali
	{
		err_db = SpecParamSearchID(P,P1.getId()-1);
		if (err_db == ERR_OK)
		{
			d1=grid->site.dist(P,err);
		}
		else return err_db;

		err_db = SpecParamSearchID(P,P1.getId()+1);
		if (err_db == ERR_OK)
		{
			d2=grid->site.dist(P,err);
		}
		else return err_db;
		if (d1 < d2)
		{
             // Vericale Sinistra
			 grid->P2 = P1;  grid->P4 = P2;

			 err_db = SpecParamSearchID(P,P1.getId()-1);
			 if (err_db != ERR_OK) return (err_db);
			 grid->P1 = P;

			 err_db = SpecParamSearchID(P,P2.getId()-1);
			 if (err_db != ERR_OK) return (err_db);
			 grid->P3 = P;

			 return (ERR_OK);
		}
		else
		{
             // Vericale Destra
			 grid->P1 = P1;  grid->P3 = P2;

			 err_db = SpecParamSearchID(P,P1.getId()+1);
			 if (err_db != ERR_OK) return (err_db);
			 grid->P2 = P;

			 err_db = SpecParamSearchID(P,P2.getId()+1);
			 if (err_db != ERR_OK) return (err_db);
			 grid->P4 = P;

			 return (ERR_OK);
		}
	}
	else if ((P2.getId() - P1.getId()) == 223) // Inclinato BR/TL
	{
		if (SpecParamSearchID(P,P2.getId()-1) == ERR_OK)
		{
			grid->P3 = P;
		}
		else return ERR_OUT_OF_MAP;
		if (SpecParamSearchID(P,P2.getId()-222) == ERR_OK)
		{
			grid->P2 = P;
		}
		else return ERR_OUT_OF_MAP;
		grid->P1 = P1;  grid->P4 = P2;
    }
    else if ((P2.getId() - P1.getId()) == 221) // Inclinato BL/TR
	{
		if (SpecParamSearchID(P,P2.getId()+1) == ERR_OK)
		{
			grid->P4 = P;
		}
		else return ERR_OUT_OF_MAP;
		if (SpecParamSearchID(P,P2.getId()-222) == ERR_OK)
		{
			grid->P1 = P;
		}
		else return ERR_OUT_OF_MAP;
		grid->P2 = P1;  grid->P3 = P2;
	} else 	return ERR_OUT_OF_MAP;

	// Controllo sull'ID dei punti
	if (
			((grid->P2.getId()-grid->P1.getId()) != 1)   ||
			((grid->P4.getId()-grid->P3.getId()) != 1)   ||
			((grid->P3.getId()-grid->P1.getId()) != 222) ||
			((grid->P4.getId()-grid->P2.getId()) != 222)
		) return ERR_OUT_OF_MAP;
	return ERR_OK;
}

int DbSapManager::SpecParamOnGrid(SeismicGrid* grid, const int& how){
	QString sql, str;
	QSqlQuery query;
	sql.append("SELECT ");
	sql.append("ID, LON_WGS84, LAT_WGS84, ");
	sql.append("AG30, FO30, TO30, ");
	sql.append("AG50, FO50, TO50, ");
	sql.append("AG72, FO72, TO72, ");
	sql.append("AG101, FO101, TO101, ");
	sql.append("AG140, FO140, TO140, ");
	sql.append("AG201, FO201, TO201, ");
	sql.append("AG475, FO475, TO475, ");
	sql.append("AG975, FO975, TO975, ");
	sql.append("AG2475, FO2475, TO2475");
	sql.append(" FROM  sismici WHERE ID = ");
	switch (how)
	{
        case DbSapManager::LM_P1 :
            sql.append(str.setNum(grid->P1.getId()));
            break;
        case DbSapManager::LM_P2 :
            sql.append(str.setNum(grid->P2.getId()));
            break;
        case DbSapManager::LM_P3 :
            sql.append(str.setNum(grid->P3.getId()));
            break;
        case DbSapManager::LM_P4 :
            sql.append(str.setNum(grid->P4.getId()));
            break;
	}
	query.exec(sql);
	if (!query.isActive()) { return ERR_QUERY_INACTIVE;	}
	int size=0;
	while (query.next()) {
		size++;
	}
    if (size>1)
    {
        return ERR_QUERY_NOT_UNIQUE;
    }
	query.first();
    if (!query.isValid())
    {
        return ERR_QUERY_NOT_VALID;
    }
	switch (how)
	{
	case DbSapManager::LM_P1 :
		grid->P1.set_lon(query.value(1).toDouble());
		grid->P1.set_lat(query.value(2).toDouble()); 
        grid->P1.set_SpecParam(
		SpecParam(query.value(3).toDouble(),query.value(4).toDouble(),query.value(5).toDouble()),
		SpecParam(query.value(6).toDouble(),query.value(7).toDouble(),query.value(8).toDouble()),
		SpecParam(query.value(9).toDouble(),query.value(10).toDouble(),query.value(11).toDouble()),
		SpecParam(query.value(12).toDouble(),query.value(13).toDouble(),query.value(14).toDouble()),
		SpecParam(query.value(15).toDouble(),query.value(16).toDouble(),query.value(17).toDouble()),
		SpecParam(query.value(18).toDouble(),query.value(19).toDouble(),query.value(20).toDouble()),
		SpecParam(query.value(21).toDouble(),query.value(22).toDouble(),query.value(23).toDouble()),
		SpecParam(query.value(24).toDouble(),query.value(25).toDouble(),query.value(26).toDouble()),
		SpecParam(query.value(27).toDouble(),query.value(28).toDouble(),query.value(29).toDouble())
								  );
		break;
	case DbSapManager::LM_P2 :
		grid->P2.set_lon(query.value(1).toDouble());
		grid->P2.set_lat(query.value(2).toDouble()); 
		grid->P2.set_SpecParam(
		SpecParam(query.value(3).toDouble(),query.value(4).toDouble(),query.value(5).toDouble()),
		SpecParam(query.value(6).toDouble(),query.value(7).toDouble(),query.value(8).toDouble()),
		SpecParam(query.value(9).toDouble(),query.value(10).toDouble(),query.value(11).toDouble()),
		SpecParam(query.value(12).toDouble(),query.value(13).toDouble(),query.value(14).toDouble()),
		SpecParam(query.value(15).toDouble(),query.value(16).toDouble(),query.value(17).toDouble()),
		SpecParam(query.value(18).toDouble(),query.value(19).toDouble(),query.value(20).toDouble()),
		SpecParam(query.value(21).toDouble(),query.value(22).toDouble(),query.value(23).toDouble()),
		SpecParam(query.value(24).toDouble(),query.value(25).toDouble(),query.value(26).toDouble()),
		SpecParam(query.value(27).toDouble(),query.value(28).toDouble(),query.value(29).toDouble())
								  );
		break;
	case DbSapManager::LM_P3 :
		grid->P3.set_lon(query.value(1).toDouble());
		grid->P3.set_lat(query.value(2).toDouble()); 
		grid->P3.set_SpecParam(
		SpecParam(query.value(3).toDouble(),query.value(4).toDouble(),query.value(5).toDouble()),
		SpecParam(query.value(6).toDouble(),query.value(7).toDouble(),query.value(8).toDouble()),
		SpecParam(query.value(9).toDouble(),query.value(10).toDouble(),query.value(11).toDouble()),
		SpecParam(query.value(12).toDouble(),query.value(13).toDouble(),query.value(14).toDouble()),
		SpecParam(query.value(15).toDouble(),query.value(16).toDouble(),query.value(17).toDouble()),
		SpecParam(query.value(18).toDouble(),query.value(19).toDouble(),query.value(20).toDouble()),
		SpecParam(query.value(21).toDouble(),query.value(22).toDouble(),query.value(23).toDouble()),
		SpecParam(query.value(24).toDouble(),query.value(25).toDouble(),query.value(26).toDouble()),
		SpecParam(query.value(27).toDouble(),query.value(28).toDouble(),query.value(29).toDouble())
								  );
		break;
	case DbSapManager::LM_P4 :
		grid->P4.set_lon(query.value(1).toDouble());
		grid->P4.set_lat(query.value(2).toDouble()); 
		grid->P4.set_SpecParam(
		SpecParam(query.value(3).toDouble(),query.value(4).toDouble(),query.value(5).toDouble()),
		SpecParam(query.value(6).toDouble(),query.value(7).toDouble(),query.value(8).toDouble()),
		SpecParam(query.value(9).toDouble(),query.value(10).toDouble(),query.value(11).toDouble()),
		SpecParam(query.value(12).toDouble(),query.value(13).toDouble(),query.value(14).toDouble()),
		SpecParam(query.value(15).toDouble(),query.value(16).toDouble(),query.value(17).toDouble()),
		SpecParam(query.value(18).toDouble(),query.value(19).toDouble(),query.value(20).toDouble()),
		SpecParam(query.value(21).toDouble(),query.value(22).toDouble(),query.value(23).toDouble()),
		SpecParam(query.value(24).toDouble(),query.value(25).toDouble(),query.value(26).toDouble()),
		SpecParam(query.value(27).toDouble(),query.value(28).toDouble(),query.value(29).toDouble())
								  );
		break;
	}
	return ERR_OK;
}
